package org.bugjail.dataaccess.jdbc.objects;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.bugjail.data.AccessLevel;
import org.bugjail.data.Person;
import org.bugjail.data.Project;
import org.bugjail.dataaccess.DAOFactory;
import org.bugjail.dataaccess.jdbc.connection.QueryException;
import org.bugjail.dataaccess.jdbc.connection.QueryManager;
import org.bugjail.dataaccess.objects.AccessLevelDAO;
import org.bugjail.dataaccess.objects.PersonDAO;
import org.bugjail.dataaccess.objects.ProjectDAO;

class JDBCPersonDAO implements PersonDAO {

	private final String PROPERTIES_FILE = "/org/bugjail/dataaccess/jdbc/properties/persondao.properties";
	private final String PROPERTY_INSERT = "insert";
	private final String PROPERTY_RETRIEVE_ID = "retrieve.id";
	private final String PROPERTY_RETRIEVE_LOGIN_PASS = "retrieve.login.password";
	private final String PROPERTY_RETRIEVE_ACCESSLEVELS = "retrieve.access.levels";
	private final String PROPERTY_DELETE = "delete";
	private final String PROPERTY_UPDATE = "update";
	private final String PROPERTY_REMOVE_ACCESSLEVELS = "remove.access.levels";
	private final String PROPERTY_INSERT_ACCESSLEVEL = "insert.access.level";
	private final String PROPERTY_RETRIEVE_PROJECT = "retrieve.project";

	@Override
	public List<Person> retrieveAll() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Person retrieveByLoginAndPassword(String login, String password) {
		Person p = null;
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_RETRIEVE_LOGIN_PASS);
				statement.setString(1, login);
				statement.setString(2, password);
				ResultSet rs = statement.executeQuery();
				if (rs.next()) {
					p = extractPersonFromResultSet(rs, queryManager);
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return p;
	}

	@Override
	public void updatePassword(Person person, String oldPassword, String newPassword) {
		// TODO Auto-generated method stub

	}

	@Override
	public void create(Person element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_INSERT);
				if (element.getId() == 0) {
					throw new Exception("Id can not be null");
				}
				statement.setInt(1, element.getId());
				statement.setString(2, element.getName());
				statement.setString(3, element.getLogin());
				statement.setString(4, element.getPassword());
				statement.setString(5, element.getMail());
				statement.setInt(6, 1);
				statement.executeUpdate();

				updatePersonAccessLevels(element, queryManager);

				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public void delete(Person element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_DELETE);
				statement.setInt(1, element.getId());
				statement.executeUpdate();
				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public Person retrieveById(int id) {
		Person p = null;
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_RETRIEVE_ID);
				statement.setInt(1, id);
				ResultSet rs = statement.executeQuery();
				if (rs.next()) {
					p = extractPersonFromResultSet(rs, queryManager);
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return p;
	}

	@Override
	public List<Person> retrieveByName(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void update(Person element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_UPDATE);
				statement.setString(1, element.getName());
				statement.setString(2, element.getLogin());
				statement.setString(3, element.getPassword());
				statement.setString(4, element.getMail());
				statement.setInt(5, element.getLanguage());
				if (element.getCurrentProject() != null) {
					statement.setInt(6, element.getCurrentProject().getId());
				} else {
					statement.setInt(6, 0);
				}				
				statement.setInt(7, element.getId());
				statement.executeUpdate();

				updatePersonAccessLevels(element, queryManager);

				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	private Person extractPersonFromResultSet(ResultSet rs, QueryManager queryManager) throws SQLException {
		Person p = new Person();
		p.setId(rs.getInt("id"));
		p.setName(rs.getString("name"));
		p.setMail(rs.getString("mail"));
		p.setLogin(rs.getString("login"));
		p.setPassword(rs.getString("pass"));
		p.setProjects(retrievePersonAccessLevels(p, queryManager));
		p.setCurrentProject(DAOFactory.getDAOFactory().getProjectDAO().retrieveById(rs.getInt("currentproject_id")));
		p.setLanguage(rs.getInt("language"));
		return p;
	}

	private Map<Project, AccessLevel> retrievePersonAccessLevels(Person person, QueryManager queryManager) throws SQLException {
		Map<Project, AccessLevel> projects = new HashMap<Project, AccessLevel>();
		ProjectDAO pdao = DAOFactory.getDAOFactory().getProjectDAO();
		AccessLevelDAO aldao = DAOFactory.getDAOFactory().getAccessLevelDAO();
		queryManager = new QueryManager(PROPERTIES_FILE);
		PreparedStatement statement = queryManager.getPreparedStatement(PROPERTY_RETRIEVE_ACCESSLEVELS);
		statement.setInt(1, person.getId());
		ResultSet rs = statement.executeQuery();
		while (rs.next()) {
			Project p = pdao.retrieveById(rs.getInt("project_id"));
			AccessLevel al = aldao.retrieveById(rs.getInt("accesslevel_id"));
			projects.put(p, al);
		}
		return projects;
	}

	private void updatePersonAccessLevels(Person person, QueryManager queryManager) throws Exception {
		PreparedStatement statement = queryManager.getPreparedStatement(PROPERTY_REMOVE_ACCESSLEVELS);
		statement.setInt(1, person.getId());
		statement.executeUpdate();

		Map<Project, AccessLevel> projects = person.getProjects();
		if (projects!=null) {
			for (Iterator<Project> iterator = projects.keySet().iterator(); iterator.hasNext();) {
				Project project = iterator.next();
				AccessLevel accessLevel = projects.get(project);
				if (project != null && accessLevel != null) {
					statement = queryManager
					.getPreparedStatement(PROPERTY_INSERT_ACCESSLEVEL);
					statement.setInt(1, person.getId());
					statement.setInt(2, project.getId());
					statement.setInt(3, accessLevel.getId());
					statement.executeUpdate();
				}
			}
		}
	}

	@Override
	public List<Person> retrieveByProject(Project project) {
		List<Person> persons = new LinkedList<Person>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_PROJECT);
				statement.setInt(1, project.getId());
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					persons.add(extractPersonFromResultSet(rs, queryManager));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return persons;
	}
}
